if exists(select * from sys.databases db where db.name = 'ServiceAss2')
use master
drop database ServiceAss2
go
create database ServiceAss2
go
use ServiceAss2
go
create table users(
	cardnumber nvarchar(30),
	username nvarchar(30) unique,
	password nvarchar(30),
	fullname nvarchar(30),
	age int,
	balance int,
	primary key(cardnumber)
)
go
--drop proc users
create proc InsertUser
	@cardnumber nvarchar(30),
	@username nvarchar(30),
	@password nvarchar(30),
	@fullname nvarchar(30),
	@age int,
	@balance bigint
as
	insert into users values(@cardnumber,@username,HashBytes('MD5',@password),@fullname,@age,@balance)
go
exec InsertUser 'ABANK0001', 'admin','123456','administrator','12',100000
go
create proc Payment
	@cardnumber nvarchar(30),
	@password nvarchar(30),
	@fullname nvarchar(30),
	@money bigint
as
	declare @balance int
	if exists (select * from users where cardnumber = @cardnumber and password = HashBytes('MD5',@password) and fullname = @fullname)
	begin 
		select @balance = balance from users where cardnumber = @cardnumber
		update users set balance = @balance - @money where cardnumber = @cardnumber		
	end
go
exec Payment 'ABANK0001','123456','administrator',100
go 
select * from users